﻿CREATE PROCEDURE dbo.SP_MemberJobs_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @EmployedFrom DateTime   =  NULL 
, @EmployedTo DateTime   =  NULL 
, @Employeer NVarChar (50)  =  NULL 
, @IsCurrentJob Bit   =  NULL 
, @JobId Int   =  NULL 
, @MemberId Int   =  NULL 
, @Position NVarChar (50)  =  NULL 
	AS
			WITH CTE_MemberJobs AS
			( 
			SELECT
 							EmployedFrom,
								EmployedTo,
								Employeer,
								IsCurrentJob,
								JobId,
								MemberId,
								Position,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume='EmployedFrom' THEN EmployedFrom
												WHEN @OrderColume='EmployedTo' THEN EmployedTo
						 
						WHEN @OrderColume='Employeer' THEN Employeer
												WHEN @OrderColume='IsCurrentJob' THEN IsCurrentJob
												WHEN @OrderColume='JobId' THEN JobId
												WHEN @OrderColume='MemberId' THEN MemberId
						 
						WHEN @OrderColume='Position' THEN Position
									ELSE 
							    JobId
					        	END
			) AS "RowNumber"
  			FROM MemberJobs 
			where 
								(EmployedFrom = @EmployedFrom OR @EmployedFrom Is Null)
										AND(EmployedTo = @EmployedTo OR @EmployedTo Is Null)
										AND(Employeer LIKE @Employeer OR @Employeer Is Null)
										AND(IsCurrentJob = @IsCurrentJob OR @IsCurrentJob Is Null)
										AND(JobId = @JobId OR @JobId Is Null)
										AND(MemberId = @MemberId OR @MemberId Is Null)
										AND(Position LIKE @Position OR @Position Is Null)
								)
			SELECT  *
			FROM CTE_MemberJobs
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)
			